﻿using System;
using System.Collections.Generic;

using System.Text;
using Portal.Interface;
using Portal.Environment;
using Portal.Language;
using System.Data;
using System.Data.SqlClient;

namespace Portal.CMS
{
    public class LawDocumentBLL
    {
        public string ConnectionString
        {
            get
            {
                return Environments.ConnectionString; 
            }
        }

        public LawDocumentCls CreateModel(string LawDocumentId)
        {
            return CreateModel(ConnectionString, LawDocumentId);
        }


        public LawDocumentCls CreateModel(string ConnectionString,string LawDocumentId)
        {
            string Query = " select TableLawDocument.* from TableLawDocument where LawDocumentId = '" + LawDocumentId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(ConnectionString));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            SqlDataReader SqlDataReader = SqlCommand.ExecuteReader();
            if (!SqlDataReader.HasRows)
            {
                throw new Exception("Không tìm thấy bản ghi LawDocumentId có mã: " + LawDocumentId);
            }

            SqlDataReader.Read();
            LawDocumentCls tLawDocumentCls = new LawDocumentCls();
            tLawDocumentCls.LawDocumentId = SqlDataReader.GetString(SqlDataReader.GetOrdinal("LawDocumentId"));
            tLawDocumentCls.Subject = SqlDataReader.GetString(SqlDataReader.GetOrdinal("Subject"));
            tLawDocumentCls.Owner = SqlDataReader.GetString(SqlDataReader.GetOrdinal("Owner"));
            tLawDocumentCls.belongSite = SqlDataReader.GetString(SqlDataReader.GetOrdinal("belongSite"));
            try
            {
                tLawDocumentCls.ShowInMenu = SqlDataReader.GetInt32(SqlDataReader.GetOrdinal("ShowInMenu"));
            }
            catch { }
            try
            {
                tLawDocumentCls.Alias = SqlDataReader.GetString(SqlDataReader.GetOrdinal("Alias"));
            }
            catch { }
            return tLawDocumentCls;
        }

        public LawDocumentItemCls CreateModelItemInc(string LawDocumentItemId)
        {
            try
            {
                string Query = "Update TableLawDocumentItem set TotalDownloaded=TotalDownloaded+1 where LawDocumentItemId=@LawDocumentItemId";
                SqlConnection SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                SqlCommand sqlCommand = SqlConnection.CreateCommand();
                sqlCommand.CommandText = Query;
                sqlCommand.Parameters.Add("@LawDocumentItemId", SqlDbType.VarChar).Value = LawDocumentItemId;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ex.Source = "";
            }
            return CreateModelItem(LawDocumentItemId);
        }

        public LawDocumentItemCls CreateModelItem(string LawDocumentItemId)
        {
            return CreateModelItem(ConnectionString, LawDocumentItemId);
        }

        public LawDocumentItemCls CreateModelItem(string ConnectionString,string LawDocumentItemId)
        {
            string Query = " select TableLawDocumentItem.* from TableLawDocumentItem where LawDocumentItemId= '" + LawDocumentItemId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(ConnectionString));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);
            if (dtResult.Rows.Count == 0) return null;

            LawDocumentItemCls tLawDocumentItemCls = new LawDocumentItemCls();
            tLawDocumentItemCls.LawDocumentItemId = LawDocumentItemId;
            tLawDocumentItemCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
            tLawDocumentItemCls.Body = (string)dtResult.Rows[0]["Body"];
            tLawDocumentItemCls.Subject = (string)dtResult.Rows[0]["Subject"];
            tLawDocumentItemCls.Code = (string)dtResult.Rows[0]["Code"];
            try
            {
                tLawDocumentItemCls.EffectedDate = (DateTime)dtResult.Rows[0]["EffectedDate"];
            }
            catch { }
            try
            {
                tLawDocumentItemCls.LawDocumentId = (string)dtResult.Rows[0]["fk_LawDocument_Id"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.ExpiredDate = (DateTime)dtResult.Rows[0]["ExpiredDate"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.Owner = (string)dtResult.Rows[0]["Owner"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.Keyword = (string)dtResult.Rows[0]["Keyword"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.LawDocumentItemId = (string)dtResult.Rows[0]["fk_LawDocumentItem_Id"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.LawDocumentOfficeId = (string)dtResult.Rows[0]["fk_LawDocumentOffice_Id"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.LawDocumentSignerId = (string)dtResult.Rows[0]["fk_LawDocumentSigner_Id"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.RealFile = (string)dtResult.Rows[0]["RealFile"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.LawDocumentTypeId = (string)dtResult.Rows[0]["fk_LawDocumentType_Id"];
            }
            catch { }

            try
            {
                tLawDocumentItemCls.ReleasedDate = (DateTime)dtResult.Rows[0]["ReleasedDate"];
            }
            catch { }
            try
            {
                tLawDocumentItemCls.EntryDate = (DateTime)dtResult.Rows[0]["EntryDate"];
            }
            catch { }

            return tLawDocumentItemCls;
        }

        public DataTable ReadingLawDocument(string Keyword)
        {
            DataTable dataTable = new DataTable();
            Keyword = FuncUtilities.CorrectForSearch(Keyword);
            string Query = "select LawDocumentId,Alias,Subject from TableLawDocument where 1=1 ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and Subject like N'%" + Keyword + "%'";
            }
            Query += " order by [Subject]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable SearchingItems(
            string LawDocumentId, 
            string Code, 
            string Subject, 
            string LawDocumentSignerId, 
            string LawDocumentTypeId, 
            string LawDocumentOfficeId,
            string Keyword)
        {
            DataTable dataTable = new DataTable();
            Code = FuncUtilities.CorrectForSearch(Code);
            Subject = FuncUtilities.CorrectForSearch(Subject);
            Keyword = FuncUtilities.CorrectForSearch(Keyword);
            string Query = " select LawDocumentItemId,LawDocument=TableLawDocument.Subject, Code,Subject=TableLawDocumentItem.Subject,Body,Keyword,ReleasedDate,EffectedDate,ExpiredDate ,Office=TableLawDocumentOffice.Subject,DocType=TableLawDocumentType.Subject,TotalDownloaded=IsNull(TotalDownloaded,0),Signer=TableLawDocumentSigner.Subject" +
                           "    from TableLawDocumentItem " +
                           "        left outer join TableLawDocument on TableLawDocument.LawDocumentId = fk_LawDocument_Id" +
                           "        left outer join TableLawDocumentType on TableLawDocumentType.LawDocumentTypeId = fk_LawDocumentType_Id"+
                           "        left outer join TableLawDocumentOffice on TableLawDocumentOffice.LawDocumentOfficeId = fk_LawDocumentOffice_Id" +
                           "        left outer join TableLawDocumentSigner on TableLawDocumentSigner.LawDocumentSignerId = fk_LawDocumentSigner_Id" +
                           "    where 1=1";
            if (LawDocumentId.Trim().Length > 0)
            {
                Query += " and fk_LawDocument_Id = '" + LawDocumentId + "'";
            }
            if (Code.Trim().Length > 0)
            {
                Query += " and TableLawDocumentItem.Code like N'%" + Code + "%'";
            }
            if (Keyword.Trim().Length > 0)
            {
                Query += " and TableLawDocumentItem.Keyword like N'%" + Keyword + "%'";
            }
            if (Subject.Trim().Length > 0)
            {
                Query += " and TableLawDocumentItem.Subject like N'%" + Subject + "%'";
            }
            if (LawDocumentSignerId.Trim().Length > 0)
            {
                Query += " and fk_LawDocumentSigner_Id = '" + LawDocumentSignerId + "'";
            }
            if (LawDocumentTypeId.Trim().Length > 0)
            {
                Query += " and fk_LawDocumentType_Id = '" + LawDocumentTypeId + "'";
            }
            if (LawDocumentOfficeId.Trim().Length > 0)
            {
                Query += " and fk_LawDocumentOffice_Id = '" + LawDocumentOfficeId + "'";
            }
            Query += " order by TableLawDocumentItem.EntryDate DESC";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingForSyncFromTool(string ConnectionString,string belongSite)
        {
            DataTable dataTable = new DataTable();
            string Query = " select LawDocumentItemId from TableLawDocumentItem where belongSite='"+belongSite+"'";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));
            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingLawDocumentItemForSync(
            string LawDocumentId)
        {
            DataTable dataTable = new DataTable();
            string Query = " select LawDocumentItemId from TableLawDocumentItem where fk_LawDocument_Id='" + LawDocumentId + "'";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));
            da.Fill(dataTable);
            return dataTable;
        }

        public string CreateLawDocument(
            SqlTransaction _SqlTransaction,
            string Alias,
            string Subject,
            string belongSite,
            int    ShowInMenu,
            string owner)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewLawDocumentId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }



            string Query = "Insert into TableLawDocument(LawDocumentId,Alias,Subject,owner,ShowInMenu,belongSite) values (@LawDocumentId,@Alias, @Subject,@owner,@ShowInMenu,@belongSite)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentId"].Value = NewLawDocumentId;

            TempSqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Alias"].Value = Alias;


            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;

            

            TempSqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Owner"].Value = owner;

            TempSqlCommand.Parameters.Add("@ShowInMenu", SqlDbType.Int);
            TempSqlCommand.Parameters["@ShowInMenu"].Value = ShowInMenu;


            TempSqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@belongSite"].Value = belongSite;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewLawDocumentId;
        }



        public void UpdateLawDocument(SqlTransaction _SqlTransaction,
            string LawDocumentId,
            string Alias,
            string Subject,
            int ShowInMenu)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableLawDocument set Alias=@Alias, Subject=@Subject,ShowInMenu=@ShowInMenu where LawDocumentId=@LawDocumentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentId"].Value = LawDocumentId;

            TempSqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Alias"].Value = Alias;

            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;

            TempSqlCommand.Parameters.Add("@ShowInMenu", SqlDbType.Int);
            TempSqlCommand.Parameters["@ShowInMenu"].Value = ShowInMenu;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteLawDocument(SqlTransaction _SqlTransaction,
            string LawDocumentId)
        {
            LawDocumentCls oLawDocumentCls = new LawDocumentBLL().CreateModel(LawDocumentId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            
            string Query = "Delete TableLawDocument where LawDocumentId=@LawDocumentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentId"].Value = LawDocumentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oLawDocumentCls != null)
                {
                    SubjectDeleted = oLawDocumentCls.Subject;
                }
                Environments.AddDeletedRecordForSync(_SqlTransaction, "TableLawDocument","Nhóm văn bản pháp quy", LawDocumentId, SubjectDeleted);
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void DeleteLawDocumentItem(SqlTransaction _SqlTransaction,
            string LawDocumentItemId)
        {
            LawDocumentItemCls oLawDocumentItemCls = new LawDocumentBLL().CreateModelItem(LawDocumentItemId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            

            string Query = "Delete TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentItemId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@LawDocumentItemId"].Value = LawDocumentItemId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oLawDocumentItemCls != null)
                {
                    SubjectDeleted = oLawDocumentItemCls.Subject;
                }
                Environments.AddDeletedRecordForSync(_SqlTransaction, "TableLawDocumentItem","Văn bản pháp quy", LawDocumentItemId,SubjectDeleted);
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public string CreateLawDocumentItem(
            SqlTransaction _SqlTransaction,
                 string LawDocumentId,
                 string belongSite,
                 string Code,
                 string Subject,
                 string Keyword,
                 string Body,
                 string LawDocumentTypeId,
                 string LawDocumentOfficeId,
                 string LawDocumentSignerId,
                 string ReleasedDate,
                 string EffectedDate,
                 string ExpiredDate,
                 string EntryDate)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewLawDocumentItemId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableLawDocumentItem(LawDocumentItemId,fk_LawDocument_Id,Code,Subject,ReleasedDate,EffectedDate,ExpiredDate,fk_LawDocumentSigner_Id,fk_LawDocumentType_Id,Keyword,Body,fk_LawDocumentOffice_Id,belongSite,EntryDate) values (@LawDocumentItemId,@fk_LawDocument_Id,@Code,@Subject,@ReleasedDate,@EffectedDate,@ExpiredDate,@fk_LawDocumentSigner_Id,@fk_LawDocumentType_Id,@Keyword,@Body,@fk_LawDocumentOffice_Id,@belongSite,@EntryDate)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentItemId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentItemId"].Value = NewLawDocumentItemId;

            TempSqlCommand.Parameters.Add("@fk_LawDocument_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocument_Id"].Value = LawDocumentId;

            TempSqlCommand.Parameters.Add("@Code", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Code"].Value = Code;


            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;


            TempSqlCommand.Parameters.Add("@Body", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Body"].Value = Body;


            TempSqlCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Keyword"].Value = Keyword;

            TempSqlCommand.Parameters.Add("@ReleasedDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@ReleasedDate"].Value = FuncUtilities.VNDateToDate(ReleasedDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@ReleasedDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@EffectedDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@EffectedDate"].Value = FuncUtilities.VNDateToDate(EffectedDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@EffectedDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@ExpiredDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@ExpiredDate"].Value = FuncUtilities.VNDateToDate(ExpiredDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@ExpiredDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@EntryDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@EntryDate"].Value = FuncUtilities.VNDateToDate(EntryDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@EntryDate"].Value = DBNull.Value;
            }


            TempSqlCommand.Parameters.Add("@fk_LawDocumentSigner_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentSigner_Id"].Value = LawDocumentSignerId;
            
            TempSqlCommand.Parameters.Add("@fk_LawDocumentType_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentType_Id"].Value = LawDocumentTypeId;

            TempSqlCommand.Parameters.Add("@fk_LawDocumentOffice_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentOffice_Id"].Value = LawDocumentOfficeId;

            TempSqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@belongSite"].Value = belongSite;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewLawDocumentItemId;
        }



        public void SaveLawDocumentItem(
            SqlTransaction _SqlTransaction,
                 string LawDocumentItemId,
                 string Code,
                 string Subject,
                 string Keyword,
                 string Body,
                 string LawDocumentTypeId,
                 string LawDocumentOfficeId,
                 string LawDocumentSignerId,
                 string ReleasedDate,
                 string EffectedDate,
                 string ExpiredDate,
                 string EntryDate)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            string Query = " Update TableLawDocumentItem set EntryDate=@EntryDate,Code=@Code,Subject=@Subject,ReleasedDate=@ReleasedDate,EffectedDate=@EffectedDate,ExpiredDate=@ExpiredDate,fk_LawDocumentSigner_Id=@fk_LawDocumentSigner_Id,fk_LawDocumentType_Id=@fk_LawDocumentType_Id,Keyword=@Keyword,Body=@Body,fk_LawDocumentOffice_Id=@fk_LawDocumentOffice_Id where LawDocumentItemId=@LawDocumentItemId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentItemId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@LawDocumentItemId"].Value = LawDocumentItemId;

            TempSqlCommand.Parameters.Add("@Code", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Code"].Value = Code;


            TempSqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Subject"].Value = Subject;


            TempSqlCommand.Parameters.Add("@Body", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Body"].Value = Body;


            TempSqlCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Keyword"].Value = Keyword;

            TempSqlCommand.Parameters.Add("@ReleasedDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@ReleasedDate"].Value = FuncUtilities.VNDateToDate(ReleasedDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@ReleasedDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@EffectedDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@EffectedDate"].Value = FuncUtilities.VNDateToDate(EffectedDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@EffectedDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@ExpiredDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@ExpiredDate"].Value = FuncUtilities.VNDateToDate(ExpiredDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@ExpiredDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@EntryDate", SqlDbType.DateTime);
            try
            {
                TempSqlCommand.Parameters["@EntryDate"].Value = FuncUtilities.VNDateToDate(EntryDate);
            }
            catch
            {
                TempSqlCommand.Parameters["@EntryDate"].Value = DBNull.Value;
            }

            TempSqlCommand.Parameters.Add("@fk_LawDocumentSigner_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentSigner_Id"].Value = LawDocumentSignerId;

            TempSqlCommand.Parameters.Add("@fk_LawDocumentType_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentType_Id"].Value = LawDocumentTypeId;

            TempSqlCommand.Parameters.Add("@fk_LawDocumentOffice_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_LawDocumentOffice_Id"].Value = LawDocumentOfficeId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void UpdateAttachFile(SqlTransaction _SqlTransaction,
            string LawDocumentItemId,
            byte[] bytes,
            string RealFile)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(ConnectionString);
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableLawDocumentItem set AttachFile=@AttachFile,RealFile=@RealFile where LawDocumentItemId=@LawDocumentItemId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@LawDocumentItemId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@LawDocumentItemId"].Value = LawDocumentItemId;

            TempSqlCommand.Parameters.Add("@AttachFile", SqlDbType.Image);
            TempSqlCommand.Parameters["@AttachFile"].Value = bytes;

            TempSqlCommand.Parameters.Add("@RealFile", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@RealFile"].Value = RealFile;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SyncWithServer(
            string ConnectionString,
            string LawDocumentItemId,
            bool   UseWsForDeploy,
            string ActiveLanguage,
            string PathRoot,
            string WorkingPath,
            string PortalAlias,
            string WsUrl,
            string WsSecurityCode)
        {
            LawDocumentItemCls LawDocumentItemCls = new LawDocumentBLL().CreateModelItem(ConnectionString, LawDocumentItemId);
            LawDocumentCls LawDocumentCls = new LawDocumentBLL().CreateModel(ConnectionString, LawDocumentItemCls.LawDocumentId);

            DataSet ds = new DataSet();
            string Query = "";
            if (UseWsForDeploy)
            {
                Query =
                    " select * from TableLawDocument where LawDocumentId in (select fk_LawDocument_Id from TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId)" +
                    " select * from TableLawDocumentOffice where LawDocumentOfficeId in (select fk_LawDocumentOffice_Id from TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId)" +
                    " select * from TableLawDocumentSigner where LawDocumentSignerId in (select fk_LawDocumentSigner_Id from TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId)" +
                    " select * from TableLawDocumentType where LawDocumentTypeId in (select fk_LawDocumentType_Id from TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId)" +
                    " select * from TableLawDocumentItem where LawDocumentItemId=@LawDocumentItemId";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@LawDocumentItemId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@LawDocumentItemId"].Value = LawDocumentItemId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableLawDocument";
                ds.Tables[1].TableName = "TableLawDocumentItem";

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Portal.Environment.Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    LawDocumentItemId,
                    LawDocumentCls.belongSite,
                    XmlData,
                    null,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.LawDocumentItem);
                if (oWsReturnParamCls.Error) throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
            }
        }


        /// <summary>
        /// Sync nhóm tin lên máy chủ (GroupNewsItem)
        /// </summary>
        /// <param name="GroupNewsItemId"></param>
        public void SyncWithServer(
            string LawDocumentItemId)
        {
            string WsUrl = Environments.GetConfigFromXml("AppWs");
            string WsSecurityCode = Portal.Environment.Environments.WsSecurityCode;
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            SyncWithServer(
                    Environments.ConnectionString,
                    LawDocumentItemId,
                    UseWsForDeploy,
                    Environments.ActiveLanguage,
                    Environments.PathRoot,
                    Environments.WorkingPath,
                    Environments.PortalAlias,
                    WsUrl,
                    WsSecurityCode
                );
        }




        public void SyncLawDocument(
            string LawDocumentId)
        {
            DataTable dtItems = ReadingLawDocumentItemForSync(LawDocumentId);
            for (int iIndex = 0; iIndex < dtItems.Rows.Count; iIndex++)
            {
                string LawDocumentItemId = (string)dtItems.Rows[iIndex]["LawDocumentItemId"];
                SyncWithServer(LawDocumentItemId);
            }
        }
    }
}
